<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Admin\Contract;
use App\Admin\Client;
use DB;
use Illuminate\Support\Facades\Schema;
use QrCode;
use Log;

// use App\Exports\UsersExport;
// use Maatwebsite\Excel\Facades\Excel;

class ContractController extends Controller
{
    public static function dbStat() {
        return [
            'status' => [
                //0-受理中，1-受理完成，5-试验中，8-试验完成，11-审核完成，13-批准驳回，14-批准完成，17-全部检测工作完成，等待退样，20-退样完成
                0  => '受理中',
                1  => '待试验',
                5  => '试验中',
                8  => '待审核',
                11 => '待批准',
                13 => '驳回',
                14 => '批准完成',
                17 => '待退样',
                20 => '退样完成',
            ],
            'status_badge' => [
                0  => 'warning',
                1  => 'primary',
                5  => 'success',
                8  => 'success',
                11 => 'success',
                13 => 'danger',
                14 => 'success',
                17 => 'success',
                20 => 'default',
            ]
        ];
    }

   /* 检测委托单 导出Excel */
    public function export(Request $request) {
        # 此数组包含的项目将使用定制报告，否则使用通用报告
        $arr = [
            'NanChongHengTong',
            'HangZhouYongTeng',
        ];
        $app_name = config('constant.app_name');

        if(!in_array($app_name, $arr)) {
            $app_name = "Public";
        }

        $className = "App\\Http\\Controllers\\Admin\\ExportContract\\ExportContract{$app_name}Controller";
        $exportReport = new $className();
        return $exportReport->export($request);
    }

    /* 检测分发单 导出Excel */
    public function exportFF(Request $request) {
        $className = "App\\Http\\Controllers\\Admin\\ExportContract\\ExportContractFFController";
        $exportReport = new $className();
        return $exportReport->export($request);
    }

    /* 样品退回单 导出Excel */
    public function exportBack(Request $request) {
        $contract_id = $request->id;
        $data_gaiyao = DB::table('a_contract_gaiyao')->where('contract_id', $contract_id)->orderBy('id', 'asc')->get();
        if($data_gaiyao->isEmpty()) {
            return '内容为空';
        }
        $data_contract = DB::table('a_contract')->find($contract_id);
        // dd($data_contract);

        // dd(base_path());
        // dd(public_path());
        $template_file = './storage/ExcelTemplate/样品退回单.xls'; //考虑到兼容Linux，斜杠用“/”
        if(!file_exists($template_file)) {
            return '模板文件不存在';
        }
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
        $worksheet = $spreadsheet->getSheet(0); //按索引获取表单，从 0 开始
        /* ---------------- 二维码 -------------------- */
        $domain = $request->server('HTTP_HOST'); //域名
        $contract_key = DB::table('a_status_time')->where('contract_id', $contract_id)->value('key'); //批次的密钥
        $url_name = 'http://'.$domain."/viewStatus?id={$contract_id}&k={$contract_key}";
        $qr_code_path = "./QrCodeImage/QrCode{$contract_id}";

        $qrCode = QrCode::format('png');
        $qrCode = $qrCode->size(150);
        $qrCode = $qrCode->margin(0);
        $qrCode->generate($url_name, $qr_code_path);

        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
        $drawing->setPath($qr_code_path);//图片的路径
        $drawing->setHeight(93);//高度 保持宽高比
        $drawing->setOffsetX(5);
        $drawing->setCoordinates('M4');//指定位置

        // 要将上述图形添加到工作表，请使用以下代码段。PhpSpreadsheet创建绘图和工作表之间的链接：
        $drawing->setWorksheet($worksheet);

        /* ---------------- 二维码 -------------------- */

        $sample_chuli = $data_contract->sample_chuli;//不合格样品处理

        $get_report = $data_contract->get_report;//取报告方式
        $setting = DB::table('setting')->find(1);
        $worksheet->getCell('A1')->setValue($setting->name_rep);
        $worksheet->getCell('K3')->setValue($data_contract->batchNum); //合同编号
        $worksheet->getCell('B4')->setValue($data_contract->entrusted_company); //委托单位
        $worksheet->getCell('K4')->setValue($data_contract->test_kind); //检测性质
        $worksheet->getCell('B5')->setValue($data_contract->client_addr); //单位地址
        $worksheet->getCell('K5')->setValue($data_contract->client_contact); //联系人
        $worksheet->getCell('B6')->setValue($data_contract->client_name); //送检单位
        $worksheet->getCell('K6')->setValue($data_contract->client_tel); //联系电话

        $first_flag = false; //第一次循环的标志
        $rowIndex = 9;
        $totalcount = 0;
        foreach($data_gaiyao as $key => $val) {
            if($first_flag) {
                $worksheet->insertNewRowBefore($rowIndex, 1); //在第10行之前插入1个新行
                $worksheet->mergeCells('C'.$rowIndex.':D'.$rowIndex); //合并单元格--电压等级+分类
                $worksheet->mergeCells('G'.$rowIndex.':L'.$rowIndex); //合并单元格--检验项目
                $worksheet->mergeCells('M'.$rowIndex.':N'.$rowIndex); //合并单元格--检验标准
            }

            if($val->class === '/') {
                # 电压等级
                $voltage_class = "{$val->voltage}";
            }
            else if($val->voltage === '/') {
                # 分类
                $voltage_class = "{$val->class}";
            }
            else {
                # 电压等级+分类
                $voltage_class = "{$val->voltage}/{$val->class}";
            }

            $worksheet->setCellValueByColumnAndRow(1, $rowIndex, $key + 1); //序号
            $worksheet->setCellValueByColumnAndRow(2, $rowIndex, $val->tool_name); //样品名称
            $worksheet->setCellValueByColumnAndRow(3, $rowIndex, $voltage_class); //电压等级+分类
            $worksheet->setCellValueByColumnAndRow(5, $rowIndex, $val->count); //数量
            $worksheet->setCellValueByColumnAndRow(6, $rowIndex, $val->unit); //单位
            $worksheet->setCellValueByColumnAndRow(7, $rowIndex, $val->item_names); //检验项目
            // $worksheet->setCellValueByColumnAndRow(56, $rowIndex, $val->items); //检验项目 --假的
            $worksheet->setCellValueByColumnAndRow(13, $rowIndex, $val->std_num); //检验标准

            // break;
            $first_flag = true;
            $rowIndex ++;
            $totalcount += $val->count;
        }
        $worksheet->setCellValueByColumnAndRow(5, $rowIndex, $totalcount); //合计
        //不合格的样品
        $unqualified = DB::table('a_sample_info')->where('contract_id', $contract_id)->where('res', 2)->select('bar_code')->get()->toArray();
        $barcodes = array_column($unqualified, 'bar_code'); //条形码
        $unqcount = count($unqualified);
        $percent = round(($totalcount - $unqcount)/$totalcount*100, 2);

        $richText = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
        $richText->createText('该批次共检测'.$totalcount.'件，其中不合格'.$unqcount.'件，');
        $payable = $richText->createTextRun('合格率：'.$percent.'%');
        $payable->getFont()->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED));
        if($unqcount > 0) {
            $richText->createText('，不合格样品条形码为'.implode('、', $barcodes));
        }
        $worksheet->setCellValueByColumnAndRow(2, $rowIndex + 2, $richText); //备注
        $worksheet->setTitle($data_contract->batchNum.'样品退回单');
        //----------直接在浏览器下载----start
        $file_name = "样品退回单".$data_contract->batchNum.".xls"; //输出的文件名称
        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出Excel 07文件
        header('Content-Type: application/vnd.ms-excel'); //告诉浏览器输出Excel 03版本文件
        header('Content-Disposition: attachment;filename='.urlencode($file_name)); //告诉浏览器输出的文件名称
        header('Cache-Control: max-age=0'); //禁止缓存
        //----------直接在浏览器下载----end

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        // $writer->save('write.xls');//将Excel文件保存在服务器的磁盘上
        $writer->save('php://output'); //直接在浏览器下载

        /* //未测试通过
        // $writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Mpdf');
        $writer->setPreCalculateFormulas(false);//禁用公式预先计算
        $writer->save("05featuredemo.pdf"); */

        //为了防止内存泄露，建议用完手动清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        unlink($qr_code_path);//删除二维码图片  节省硬盘空间
    }

    /* 合同列表展示 */
    public function index(Request $request) {
        if($request->expectsJson()) {
            // return response()->json($result);
        }
        else {
            // $data = Contract::orderBy('id', 'desc')->get();
            $sql = "SELECT tabA.*,tabB.sum_count FROM a_contract AS tabA LEFT JOIN (SELECT a_contract_id,SUM(count) AS sum_count FROM a_contract_gaiyao GROUP BY a_contract_id) AS tabB ON tabA.id=tabB.a_contract_id ORDER BY id desc";
            $data = DB::select($sql);
            // dd($data);
            return view('admin.contract.index', compact('data'));
        }
    }

    /* 所有合同列表展示 */
    public function indexAll(Request $request) {
        if($request->expectsJson()) {
            $contract = new Contract();
            $where = [
                ['status', '<', '20'],
                ['is_del', '=', '0'],
            ];
            if(config('constant.app_name') !== 'NingXiaNingDongJianCe'){
                if(!isAdmin()){
                    array_push($where,['creator', '=', auth('admin')->user()->realname]);
                }
            }
            $result = $contract->getTableData($request->all(), null, ['id', 'desc'], $where);
            return response()->json($result);
        }
        else {
            $data['dbstat'] = self::dbStat();
            return view('admin.contract.indexAll',compact('data'));
        }
    }

    /* 回收站列表展示 */
    public function recycleBin(Request $request) {
        if($request->expectsJson()) {
            $contract = new Contract();
            $result = $contract->getTableData($request->all(), null, ['id', 'desc'], [['is_del', '=', '1']]);
            return response()->json($result);
        }
        else {
            return view('admin.contract.recycleBin');
        }
    }

    /* 到期预警 */
    public function expire(Request $request) {
        $data = [];
        $settingdata = DB::table('setting_sys')->where('key', 'warn_days_limit')->value('value');
        $data['setting'] = $settingdata = unserialize($settingdata);
        if($request->expectsJson()) {
            $defaultdate = date('Y-m-d', strtotime('+'.$settingdata['days'].' days'));
            $nexttimelimit = $request->query('next_time', $defaultdate);
            $nextMintimelimit = date('Y-m-d',strtotime('-15 days'));
            $sampledata = DB::table('a_sample_info as a')
                            ->leftJoin('a_contract as b', 'a.contract_id', '=', 'b.id')
                            ->leftJoin('a_contract_gaiyao as c', 'a.contract_gaiyao_id', '=', 'c.id')
                            ->leftJoin('a_client as d', 'd.name', '=', 'b.client_name')
                            ->whereDate('a.next_time', '>', $nextMintimelimit)
                            ->whereDate('a.next_time', '<', $nexttimelimit)
                            ->where('a.res', 1)
                            ->where('a.notice', 1)
                            ->select('a.bar_code', 'a.contract_id', 'a.contract_gaiyao_id', 'a.next_time', 'a.gqj_ID', 'b.client_name', 'c.tool_name', 'd.contact', 'd.telephone')
                            ->get();   //樣品信息
                            // dd($sampledata);
            //将样品信息整合成按照送检单位成批
            $data = $dwdata = [];
            foreach($sampledata as $k => $item) {
                $client_name = $item->client_name;
                $dwdata[$client_name][] = $item;
                in_array($client_name, $data) ? '' : array_push($data, $client_name);
            }
            $result['data'] = $data;
            $result['dwdata'] = $dwdata;
            return response()->json($result);
        }
        return view('admin.contract.expire', compact('data'));
    }

    /* 查看所有批次 */
    public function viewAllBatch(Request $request) {
        if($request->expectsJson()) {
            return response()->json($this->list());
        }
        else {
            return view('admin.contract.viewAllBatch');
        }
    }

    //批次列表
    private function list(){
        $contract = new Contract();
        $params = request()->all();
        //            $params['columns'][4]['search']['value'] = 17;
        //            $params['columns'][4]['search']['regex'] = true;
        $result = $contract->getTableData($params, null, ['id', 'desc'], [['is_del', '=', '0']]);
        return $result;
    }

    /* 支付 */
    public function goPay(Request $request) {
        $id = $request->id;
        if(!$id) {
            return response('条件不充分', 404);
        }
        $contract = new Contract();
        if($request->isMethod('post')) {
            $params = $request->all();
            unset($params['_token']);
            unset($params['_form_token']);
            $params['paid_status'] = 1;
            if (!$params['paid_at']){
                return 0;
            }
            $res = $contract->where('id', $id)->update($params);
            return $res;
        }
        else {
            $data = $contract->find($id);
            return view('admin.contract.goPay', compact('data'));
        }
    }

    /* 合同列表展示（信息录入） */
    public function indexInfoLuru(Request $request) {
        $arr = [
            'HuiBangJianCe',
        ];
        $app_name = config('constant.app_name');
        if($request->expectsJson()) {
            $contract = new Contract();
            $where = [
                ['status', '>=', '0'],
                ['status', '<', '20'],
                ['is_del', '=', '0']
            ];
            if(!isAdmin() && in_array($app_name, $arr)) {
                $where[] = ['creator', '=', auth('admin')->user()->realname];
            }
            $result = $contract->getTableData($request->all(), null, ['id', 'desc'], $where);
            return response()->json($result);
        }
        else {
            $selfEdit = 0;
            if(in_array($app_name, $arr)) {
                $selfEdit = 1;
            }
            return view('admin.contract.indexInfoLuru',compact('selfEdit'));
        }
    }

    /* 合同列表展示（结果录入） */
    public function indexResLuru(Request $request) {
        // if ($request->expectsJson()) {
        //     $contract = new Contract();
        //     $result = $contract->getTableData($request->all(), null, ['id', 'desc'], [['status', '>=', '5']]);
        //     return response()->json($result);
        // } else {
        //     return view('admin.contract.indexResLuru');
        // }
        $manager_id = auth('admin')->user()->id;

        $sql = "SELECT
                    tabA.*,
                    tabC.back_count
                FROM
                    a_contract AS tabA
                    LEFT JOIN ( SELECT contract_id,count( 1 ) AS back_count FROM a_sample_info where status0=-1 GROUP BY contract_id ) AS tabC ON tabA.id = tabC.contract_id
                WHERE
                    (
                        id IN (
                            SELECT DISTINCT
                                a_item_data.contract_id
                            FROM
                                a_item_data ";
        if(!isAdmin()) {
            $sql .= "WHERE manager_id = $manager_id ";
        }
        $sql .= "
                        ) and STATUS >= 0 and STATUS < 20 and is_del = 0
                    )
                ORDER BY
                    id DESC ";
        $data = DB::select($sql);
        return view('admin.contract.indexResLuru', compact('data'));
    }

    /* 合同列表展示（审核） */
    public function indexCheck(Request $request) {
        $sql = "SELECT
                    tabA.*,
                    tabB.sum_count,
                    tabC.back_count,
                    tabD.pi_back_count
                FROM
                    a_contract as tabA
                    LEFT JOIN(SELECT contract_id, SUM(count) as sum_count FROM a_contract_gaiyao GROUP BY contract_id ) as tabB ON tabA.id = tabB.contract_id
                    LEFT JOIN(SELECT contract_id,count(1) as back_count FROM a_sample_info where status0 = -1 GROUP BY contract_id ) as tabC ON tabA.id = tabC.contract_id
                    LEFT JOIN(SELECT contract_id,count(1) as pi_back_count FROM a_reject_log where sample_info_id IS NULL GROUP BY contract_id ) as tabD ON tabA.id = tabD.contract_id
                WHERE
                STATUS >= 0 and STATUS < 20 and is_del = 0
               ";
	    if(app_name() === 'HangZhouYongTeng' && !isAdmin()){
		    $sql .= " and tabA.creator = '".auth('admin')->user()->realname."' ";
	    }
	    $sql .= " ORDER BY
                    id DESC";
        $data = DB::select($sql);
        if (!config('constant.is_lan')) {
            try{
                $this->upTopicSampleSubmitCount(0);
            }catch(\Exception $exception){
                // dd($exception->getMessage());
                Log::info($exception->getMessage());
            }
        }
        return view('admin.contract.indexCheck', compact('data'));
    }

    //发布
    private function upTopicSampleSubmitCount($count) {
        cache()->forever('sample_submit_noread_count', $count);
        $client = new \Mosquitto\Client();
        $client->setCredentials('sample_sub_user', 'sample_sub_user');
        $client->connect("127.0.0.1", 1883, 60);
        $client->onPublish(function() use ($client) {
            $client->disconnect();
        });
        $client->publish('/'.domain().'/sample_info/submit/count', $count);
    }

    /* 合同列表展示（批准） */
    public function indexApprove(Request $request) {
        $sql = "SELECT
                    tabA.*,
                    tabB.sum_count,
                    tabC.audit_man1,
                    tabD.check_start,
                    tabD.check_end
                FROM
                    a_contract as tabA
                    LEFT JOIN(SELECT contract_id, SUM(count) as sum_count FROM a_contract_gaiyao GROUP BY contract_id ) as tabB ON tabA.id = tabB.contract_id
                    LEFT JOIN(
                    SELECT
                        contract_id,
                        GROUP_CONCAT( if(audit_man != '', CONCAT(audit_man, '，'), '' ) SEPARATOR '' ) as 'audit_man1'
                    FROM
                        `a_sample_info`
                    GROUP BY
                        contract_id
                    ) as tabC ON tabA.id = tabC.contract_id
                    LEFT JOIN a_status_time as tabD ON tabA.id = tabD.contract_id
                WHERE
                STATUS >= 8 and STATUS < 20 and is_del = 0
                ";
	    if(app_name() === 'HangZhouYongTeng' && !isAdmin()){
		    $sql .= " and tabA.creator = '".auth('admin')->user()->realname."' ";
	    }
	    $sql .= " ORDER BY
                    id DESC";
        $data = DB::select($sql);
        return view('admin.contract.indexApprove', compact('data'));
    }

    /* 合同列表展示（退样） */
    public function indexSendBack(Request $request) {
        $sql = "SELECT
                    tabA.*,tabB.sum_count
                FROM
                    a_contract as tabA LEFT JOIN(SELECT contract_id,SUM(count) as sum_count FROM a_contract_gaiyao GROUP BY contract_id) as tabB ON tabA.id = tabB.contract_id
                WHERE
                (status >= 17 and status < 20 and is_del = 0)
                ORDER BY
                    id desc";
        $data = DB::select($sql);
        return view('admin.contract.indexSendBack', compact('data'));
    }

    /* 合同列表展示（检测工作全部完成） */
    public function indexTestOver(Request $request) {
        $sql = "SELECT
                    tabA.*,tabB.sum_count
                FROM
                    a_contract as tabA LEFT JOIN(SELECT contract_id,SUM(count) as sum_count FROM a_contract_gaiyao GROUP BY contract_id) as tabB ON tabA.id = tabB.contract_id
                WHERE
                (status >= 14 and status < 20 and is_del = 0)
                ORDER BY
                    id desc";
        $data = DB::select($sql);
        return view('admin.contract.indexTestOver', compact('data'));
    }

    /* 合同列表展示（打印合格证） */
    public function indexPrintPassedCard(Request $request) {
        if($request->expectsJson()) {
            $contract = new Contract();
            $where = [['is_del', '=', '0']];
	        if(app_name() === 'HangZhouYongTeng' && !isAdmin()){
		        $where[] = ['creator','=',auth('admin')->user()->realname];
	        }
	        $result = $contract->getTableData($request->all(), null, ['id', 'desc'], $where);
            return response()->json($result);
        }
        else {
            return view('admin.contract.indexPrintPassedCard');
        }
    }

    /* 合同列表展示（下载检测报告） */
    public function indexDownloadReport(Request $request) {
        $sql = "SELECT
                    tabA.*,
                    tabB.sum_count,
                    tabC.approve_end
                FROM
                    a_contract as tabA
                    LEFT JOIN(SELECT contract_id,SUM(count) as sum_count FROM a_contract_gaiyao GROUP BY contract_id) as tabB ON tabA.id = tabB.contract_id
                    LEFT JOIN a_status_time as tabC ON tabA.id = tabC.contract_id
                WHERE
                (status >= 14 and status < 20 and is_del = 0)
                ORDER BY
                    id desc";
        $data = DB::select($sql);
        return view('admin.contract.indexDownloadReport', compact('data'));
    }

    /* 合同列表展示（条形码打印） */
    public function indexPrintBarCode(Request $request) {
        if($request->expectsJson()) {
            $contract = new Contract();
            $result = $contract->getTableData($request->all(), null, ['id', 'desc'], [['status', '>=', '0'], ['status', '<', '20'], ['is_del', '=', '0']]);
            return response()->json($result);
        }
        else {
            return view('admin.contract.indexPrintBarCode');
        }
    }

    /* 合同列表展示（任务分配） */
    public function indexAssignTask(Request $request) {
        if($request->expectsJson()) {
            $contract = new Contract();
            $where = [['status', '>=', '0'], ['status', '<', '20'], ['is_del', '=', '0']];
            if(app_name() === 'HangZhouYongTeng' && !isAdmin()){
            	 $where[] = ['creator','=',auth('admin')->user()->realname];
            }
            $result = $contract->getTableData($request->all(), null, ['id', 'desc'], $where);
            return response()->json($result);
        }
        else {
            return view('admin.contract.indexAssignTask');
        }
    }

    /* 在合同内添加内容 */
    public function addsub() {
        $sample_type = DB::table('a_sample_basic_info')->where('pid', '0')->orderBy('sort')->get();
        return view('admin.contract.addsub', compact('sample_type'));
    }


    /* 合同添加 页面展示 */
    public function addShow(Request $request) {
        if($request->expectsJson()) {
            $client = new Client();
            $result = $client->getTableData($request->all(), null, ['id', 'desc '], null);
            return response()->json($result);
        }
        else {
            $data_test_kind = DB::table('a_test_kind')->orderBy('stamp', 'desc ')->get();
            $letters = json_decode(DB::table('setting_sys')->where('key', 'letterFrom ')->value('value'), true);
            return view('admin.contract.addShow', compact('data_test_kind', 'letters'));
        }
    }

    /* 合同添加
    public function add(Request $request) {
        //请求为 ajax
        if($request->expectsJson()) {
            $batchNum = data_get($request,'batchNum','');
            if ($batchNum) { //修改受理时间
                $date = data_get($request,'date','');
                $res = 0;
                if ($date) {
                    $res = DB::table('a_contract')->where('batchNum',$batchNum)->update(['created_at'=>$date]);
                }
                return $res ? 1 : 0;
            }
            //获取客户信息 id
            $client_id = $request->client_id;
            //查询出客户信息
            $client_info = DB::table('a_client')->find($client_id);
            //-----制作批次编号 例：180001
            $current_year = Date('y');
            //如果还没有批次，批次编号为 年号 + “0001”
            if(Contract::count() === 0) {
                $batchNum = $current_year."0001";
            }
            else {
                $sql = "SELECT batchNum from a_contract where id = (select max(id) from a_contract)";
                $res = DB::select($sql);
                $str_res = $res[0]->batchNum;
                $year = substr($str_res, 0, 2);
                //如果批次编号的前两位与当前年号相同
                if($year === $current_year) {
                    $num = intval(substr($str_res, 2)) + 1;
                    // $str_num = sprintf('%04.0f', $num);
                    $str_num = sprintf('%04d', $num);
                    $batchNum = $current_year.$str_num;
                }
                else {
                    $batchNum = $current_year."0001";
                }
            }
            $insertData = [
                'batchNum'          => $batchNum,
                'client_name'       => $client_info->name,//送检单位
                'entrusted_company' => $client_info->entrusted_company,//委托单位
                'client_contact'    => $client_info->contact,
                'client_tel'        => $client_info->telephone,
                'name_ABC'          => $client_info->name_ABC,//送检单位字母表示
                'gqj_ID'            => $client_info->gqj_ID,//工器具ID前8位
                'creator'           => auth('admin')->user()->realname,
                'remark'            => $request->remark == "" ? "/" : $request->remark,
                'deadline'          => $request->deadline === '' ? null : $request->deadline,
                'money'             => $request->money,
                'sample_chuli'      => $request->sample_chuli,
                'get_report'        => $request->get_report,
                'client_addr'       => $client_info->address,
                'test_kind'         => $request->test_kind//检测性质
            ];
            if(config('constant.app_name') === 'SiChuanDeYang') {
                $insertData['client_code_class'] = substr($client_info->gqj_ID, 3, 1);
            }
            DB::beginTransaction();
            try {
                $result = DB::table('a_contract')->insertGetId($insertData);
                $lastRes = $result ? 1 : 0;
                //新建一条记录，用于记录批次的状态时间
                $res1 = DB::table('a_status_time')->insert([
                    'contract_id' => $result,
                    'key'         => str_random(6) //用六位随机字符串作为密码
                ]);
                $lastRes = $res1 ? $lastRes : $res1;
                if(isset($request->letter_code) && $request->letter_code != '') {
                    $res2 = DB::table('a_contract_letter')->insert([
                        'contract_id' => $result,
                        'letter_code' => $request->letter_code,
                        'letter_no'   => $request->letter_no,
                    ]);
                    $lastRes = $res2 ? $lastRes : $res2;
                    $setting = DB::table('setting_sys')->where('key', 'letterFrom')->value('value');
                    $setting = json_decode($setting, true);
                    $setting[$request->letter_code]['used'] += 1;
                    $res3 = DB::table('setting_sys')->where('key', 'letterFrom')->update(['value' => json_encode($setting)]);
                    $lastRes = $res3 ? $lastRes : $res3;
                }

                //更新a_test_kind 表的 stamp 字段（用于将最近点击过的条目显示在前面）
                $res4 = DB::table('a_test_kind')->where('name', $request->test_kind)->update(['stamp' => time()]);
                $lastRes = $res4 ? $lastRes : $res4;

                if($lastRes) {
                    DB::commit();
                }
                else {
                    DB::rollBack();
                    return 0;
                }
            } catch(\Exception $e) {
                DB::rollBack();
                return $e->getMessage();
            }
            return $result;
        }

//          if ($request->isMethod('POST')) {
//            //  sleep (3);//睡3秒
//            //表单提交的只有3个信息(auth_name,pid,url)
//            $auth = new Auth();
//            $post_data = $request->only(['auth_name', 'pid']);
//            //通过算法制作 path 和 level，并实现整条记录的填充
//            $result = $auth->saveData($post_data);
//            //由于框架自身不支持响应bool值，所以转化一种形式
//            return $result ? '1' : '0';
//        } else {
//            // sleep (3);//睡3秒
//            //查询数据（样品种类  例：绝缘杆、绝缘靴）
//            $sample_type = DB::table('a_sample_basic_info')->where('pid', '0')->orderBy('sort')->get();
//            return view('admin.contract.add', compact('sample_type'));
//        }
    }*/
    public function add(Request $request) {

        //请求为 ajax
        //if($request->expectsJson()) {
            //获取客户信息 id
            $client_id = $request->client_id;
            //查询出客户信息
            $client_info = DB::table('a_client')->find($client_id);
            //-----制作批次编号 例：180001
            $current_year = Date('y');
            //如果还没有批次，批次编号为 年号 + “0001”
            if(Contract::count() === 0) {
                $batchNum = $current_year."0001";
            }
            else {
                $sql = "SELECT batchNum from a_contract where id = (select max(id) from a_contract)";
                $res = DB::select($sql);
                $str_res = $res[0]->batchNum;
                $year = substr($str_res, 0, 2);
                //如果批次编号的前两位与当前年号相同
                if($year === $current_year) {
                    $num = intval(substr($str_res, 2)) + 1;
                    // $str_num = sprintf('%04.0f', $num);
                    $str_num = sprintf('%04d', $num);
                    $batchNum = $current_year.$str_num;
                }
                else {
                    $batchNum = $current_year."0001";
                }
            }

            $insertData = [
                'batchNum'          => $batchNum,
                'client_name'       => $client_info->name,//送检单位
                'entrusted_company' => $client_info->entrusted_company,//委托单位
                'client_contact'    => $client_info->contact ?: '',
                'client_tel'        => $client_info->telephone  ?: '',
                'name_ABC'          => $client_info->name_ABC,//送检单位字母表示
                'gqj_ID'            => $client_info->gqj_ID  ?: '',//工器具ID前8位
                'creator'           => auth('admin')->user() ?auth('admin')->user()->realname: '检测中心',
                'remark'            => $request->remark ?: "/",
                'deadline'          => $request->deadline === '' ? null : $request->deadline,
                'money'             => $request->money  ?: 0,
                'sample_chuli'      => $request->sample_chuli  ?: '',
                'get_report'        => $request->get_report  ?: '',
                'client_addr'       => $client_info->address  ?: '',
                'test_kind'         => $request->test_kind  ?: '' //检测性质
            ];
            if(config('constant.app_name') === 'SiChuanDeYang') {
                $insertData['client_code_class'] = substr($client_info->gqj_ID, 3, 1);
            }
            DB::beginTransaction();
            try {
                $result = DB::table('a_contract')->insertGetId($insertData);
                //新建一条记录，用于记录批次的状态时间
                $res1 = DB::table('a_status_time')->insert([
                    'contract_id' => $result,
                    'key'         => str_random(6) //用六位随机字符串作为密码
                ]);
                if(isset($request->letter_code) && $request->letter_code != '') {
                    $res2 = DB::table('a_contract_letter')->insert([
                        'contract_id' => $result,
                        'letter_code' => $request->letter_code,
                        'letter_no'   => $request->letter_no,
                    ]);
                    $setting = DB::table('setting_sys')->where('key', 'letterFrom')->value('value');
                    $setting = json_decode($setting, true);
                    $setting[$request->letter_code]['used'] += 1;
                    $res3 = DB::table('setting_sys')->where('key', 'letterFrom')->update(['value' => json_encode($setting)]);
                }

                //更新a_test_kind 表的 stamp 字段（用于将最近点击过的条目显示在前面）
                $res4 = DB::table('a_test_kind')->where('name', $request->test_kind)->update(['stamp' => time()]);
                DB::commit();
            } catch(\Exception $e) {
                DB::rollBack();
                return $e->getMessage();
            }
            return $result;
        //}
    }
    //合同修改
    public function edit(Request $request) {
        $id = $request->id;
        if($request->isMethod('POST')) {
            $post_data = $request->only(['created_at', 'client_name', 'entrusted_company', 'client_contact', 'client_tel', 'client_addr', 'remark', 'deadline', 'money', 'sample_chuli', 'get_report', 'test_kind']);
            $post_data['deadline'] = $post_data['deadline'] === '' ? null : $post_data['deadline'];
            //update 返回受影响的行数
            $result = Contract::where('id', $id)->update($post_data);
            //由于框架自身不支持响应bool值，所以转化一种形式
            return $result >= 0 ? '1' : '0';
        }
        else {
            $data = Contract::find($id);

            if($data) {
                //查到了结果
                return view('admin.contract.edit', compact('data'));
            }
        }
    }

    //合同软删除
    public function softDel(Request $request) {
        $id = $request->input('id');
        // delete 返回受影响的行数
        $result = Contract::where('id', $id)->update(['is_del' => '1']);
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $result >= 0 ? '1' : '0';
    }

    //合同 恢复
    public function restore(Request $request) {
        $id = $request->input('id');
        // delete 返回受影响的行数
        $result = Contract::where('id', $id)->update(['is_del' => '0']);
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $result >= 0 ? '1' : '0';
    }

    //合同删除
    public function del(Request $request) {
        $id = $request->input('id');
        // delete 返回受影响的行数
        $result1 = Contract::where('id', $id)->delete();
        $result2 = DB::table('a_contract_gaiyao')->where('contract_id', $id)->delete();
        $result3 = DB::table('a_sample_info')->where('contract_id', $id)->delete();
        $result4 = DB::table('a_item_data')->where('contract_id', $id)->delete();
        //修改委托书规则使用条数
        if(Schema::hasTable('a_contract_letter')) {
            $contractLetter = DB::table('a_contract_letter')->where('contract_id', $id)->first();
            if($contractLetter) {
                $setting = DB::table('setting_sys')->where('key', 'letterFrom')->value('value');
                $setting = json_decode($setting, true);
                $setting[$contractLetter->letter_code]['used'] -= 1;
                DB::table('setting_sys')->where('key', 'letterFrom')->update(['value' => json_encode($setting)]);
            }
        }
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $result1 >= 0 && $result2 >= 0 && $result3 >= 0 ? '1' : '0';
    }

    //批准操作
    public function approve(Request $request) {
        $id = $request->id;
        //  update 返回受影响的行数
        $auditman = auth()->user()->realname ? auth()->user()->realname : auth()->user()->username;
        $res1 = DB::table('a_contract')->where('id', $id)->update(['status' => $request->status, 'audit_man' => $auditman]);
        //批次的状态  0-受理中，1-受理完成，5-试验中，8-试验完成，11-审核完成，13-批准驳回，14-批准完成，17-全部检测工作完成，等待退样，20-退样完成
        //记录驳回原因
        if($request->status == '13' && $request->reason !== '') {
            $data_reject = [
                'contract_id'      => intval($id),
                'content'          => trim($request->reason),
                'manager_realname' => $auditman
            ];
            DB::table('a_reject_log')->insert($data_reject);
        }
        if($request->status == '14') {
            # 如果批准通过，则记录批准完成的时间
            //查询此批次是否记录过批准完成的时间
            $approve_end = DB::table('a_status_time')->where('contract_id', $id)->value('approve_end');
            if(!$approve_end) {
                # 如果没记录过，就记录批准完成的时间
                $now_time = date("Y/m/d H:i:s"); //当前时间
                DB::table('a_status_time')->where('contract_id', $id)->update(['approve_end' => $now_time]);
            }
        }
        //由于框架自身不支持响应bool值，所以转化一种形式
        return $res1 >= 0 ? '1' : '0';
    }

    //完成操作 所有检测工作完成后，把状态改为 17，以通知退样人员退样
    public function testOver(Request $request) {
        $id = $request->id;
        //  update 返回受影响的行数
        $res1 = DB::table('a_contract')->where('id', $id)->update(['status' => '17']);

        //查询此批次是否记录过 检测报告编制完成时间
        $report_end = DB::table('a_status_time')->where('contract_id', $id)->value('report_end');
        if(!$report_end) {
            # 如果没记录过，就记录 检测报告编制完成时间
            $now_time = date("Y/m/d H:i:s"); //当前时间
            DB::table('a_status_time')->where('contract_id', $id)->update(['report_end' => $now_time]);
        }

        //由于框架自身不支持响应bool值，所以转化一种形式
        return $res1 >= 0 ? '1' : '0';
    }

    //退样操作 退样完成后，把状态改为 20
    public function sendBack(Request $request) {
        $id = $request->id;
        //  update 返回受影响的行数
        $res1 = DB::table('a_contract')->where('id', $id)->update(['status' => '20']);

        //查询此批次是否记录过 退样完成时间
        $sendback_end = DB::table('a_status_time')->where('contract_id', $id)->value('sendback_end');
        if(!$sendback_end) {
            # 如果没记录过，就记录 退样完成时间
            $now_time = date("Y/m/d H:i:s"); //当前时间
            DB::table('a_status_time')->where('contract_id', $id)->update(['sendback_end' => $now_time]);
        }

        //由于框架自身不支持响应bool值，所以转化一种形式
        return $res1 >= 0 ? '1' : '0';
    }

    //手机扫描二维码查看 检验进度
    public function viewStatus(Request $request) {
        $id = $request->id; //批次的id
        $name_rep = DB::table('setting')->where('id', '1')->value('name_rep');

        //批次创建时间
        $created_at = DB::table('a_contract')->where('id', $id)->value('created_at');
        if(!$created_at) {
            # code...
            return;
        }
        //批次的状态时间信息
        $status_time_info = DB::table('a_status_time')->where('contract_id', $id)->first();
        if($status_time_info->key != $request->k) {
            return " < h1 style = 'margin-top:200px;padding-left:50px' > 密钥错误</h1 > ";
        }
        //检测项目总量
        $total_count = DB::table('a_item_data')->where('contract_id', $id)->count();
        //检测项目 已检的数量  结论,   0-不符合，1-符合，2-未录入
        $over_count = DB::table('a_item_data')->where('contract_id', $id)->where('res', '!=', '2')->count();

        if($total_count == 0) {
            $rate = "0.0";   # 防止除数为零
        }
        else {
            //检验完成百分比
            $rate = sprintf("%.1f", $over_count*100/$total_count);
        }
        return view('admin.contract.viewStatus', compact('name_rep', 'created_at', 'status_time_info', 'rate'));
    }

    //查看检验进度--注册客户
    public function viewStatusReg(Request $request) {
        $id = $request->id; //批次的id
        $name_rep = DB::table('setting')->where('id', '1')->value('name_rep');

        //批次创建时间
        $created_at = DB::table('a_contract')->where('id', $id)->value('created_at');
        if(!$created_at) {
            # code...
            return;
        }
        //批次的状态时间信息
        $status_time_info = DB::table('a_status_time')->where('contract_id', $id)->first();
        // if ($status_time_info->key != $request->k) {
        //     return " < h1 style = 'margin-top:200px;padding-left:50px' > 密钥错误</h1 > ";
        // }
        //检测项目总量
        $total_count = DB::table('a_item_data')->where('contract_id', $id)->count();
        //检测项目 已检的数量  结论,   0-不符合，1-符合，2-未录入
        $over_count = DB::table('a_item_data')->where('contract_id', $id)->where('res', '!=', '2')->count();

        if($total_count == 0) {
            $rate = "0.0";   # 防止除数为零
        }
        else {
            //检验完成百分比
            $rate = sprintf("%.1f", $over_count*100/$total_count);
        }
        return view('admin.contract.viewStatus', compact('name_rep', 'created_at', 'status_time_info', 'rate'));
    }

    //查看批次的客户信息
    public function viewClientInfo(Request $request) {
        $contract_id = $request->id;
        $contract_info = DB::table('a_contract')->find($contract_id);
        return view('admin.contract.viewClientInfo', compact('contract_info'));
    }
}
